﻿Imports System.Configuration
Imports System.Data.SqlClient
Public Class DAL
    Private Function AccessDB(ByVal sqlComm As SqlCommand, ByVal returns As Boolean) As Object
        Dim sqlConn As New SqlConnection
        Dim sqlAdapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim connString As String = ConfigurationManager.ConnectionStrings("Internal").ToString()
        sqlConn = New SqlConnection(connString)
        Try
            sqlComm.Connection = sqlConn
            sqlConn.Open()
            If returns Then
                sqlAdapter.SelectCommand = sqlComm
                sqlAdapter.Fill(ds)
                sqlConn.Close()
                Return ds
            Else
                sqlComm.ExecuteNonQuery()
                sqlConn.Close()
                Return True
            End If
        Catch ex As Exception

        End Try
        Return False
    End Function
#Region "Customers"
    Public Function GetCustomerByLastName(ByVal info As GetCustomerByLastName) As DataSet
        Dim sqlCommand As New SqlCommand
        Dim storedProcedure As String = "GetCustomerByLastName"
        Dim ds As New DataSet
        Try
            With sqlCommand
                .CommandText = storedProcedure
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@lName", SqlDbType.NVarChar).Value = info.lName
            End With
        Catch ex As Exception

        End Try
        ds = AccessDB(sqlCommand, True)
        Return ds
    End Function
    Public Function CreateCustomer(ByVal info As CreateCustomer) As Boolean
        Dim sqlCommand As New SqlCommand
        Dim storedProcedure As String = "CreateCustomer"
        Dim retVal As Boolean = True
        Try
            With sqlCommand
                .CommandText = storedProcedure
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@fName", SqlDbType.NVarChar).Value = info.fName
                .Parameters.Add("@lName", SqlDbType.NVarChar).Value = info.lName
                .Parameters.Add("@phoneNo", SqlDbType.NVarChar).Value = info.phoneNo
                .Parameters.Add("@street", SqlDbType.NVarChar).Value = info.street
                .Parameters.Add("@state", SqlDbType.NVarChar).Value = info.state
                .Parameters.Add("@city", SqlDbType.NVarChar).Value = info.city
                .Parameters.Add("@zip", SqlDbType.NVarChar).Value = info.zip
                .Parameters.Add("@email", SqlDbType.NVarChar).Value = info.email
            End With
        Catch ex As Exception

        End Try
        retVal = AccessDB(sqlCommand, False)
        Return retVal
    End Function
    Public Function EditCustomer(ByVal info As EditCustomer) As Boolean
        Dim sqlCommand As New SqlCommand
        Dim storedProcedure As String = "EditCustomer"
        Dim retVal As Boolean = True
        Try
            With sqlCommand
                .CommandText = storedProcedure
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@customerId", SqlDbType.Int).Value = info.customerId
                .Parameters.Add("@fName", SqlDbType.NVarChar).Value = info.fName
                .Parameters.Add("@lName", SqlDbType.NVarChar).Value = info.lName
                .Parameters.Add("@phoneNo", SqlDbType.NVarChar).Value = info.phoneNo
                .Parameters.Add("@street", SqlDbType.NVarChar).Value = info.street
                .Parameters.Add("@state", SqlDbType.NVarChar).Value = info.state
                .Parameters.Add("@city", SqlDbType.NVarChar).Value = info.city
                .Parameters.Add("@zip", SqlDbType.NVarChar).Value = info.zip
                .Parameters.Add("@email", SqlDbType.NVarChar).Value = info.email
            End With
        Catch ex As Exception

        End Try
        retVal = AccessDB(sqlCommand, False)
        Return retVal
    End Function
#End Region

    
    Public Function CreateEmployee(ByVal info As CreateCustomer) As Boolean
        Dim sqlCommand As New SqlCommand
        Dim storedProcedure As String = "CreateCustomer"
        Dim retVal As Boolean = True
        Try
            With sqlCommand
                .CommandText = storedProcedure
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@fName", SqlDbType.NVarChar).Value = info.fName
                .Parameters.Add("@lName", SqlDbType.NVarChar).Value = info.lName
                .Parameters.Add("@phoneNo", SqlDbType.NVarChar).Value = info.phoneNo
                .Parameters.Add("@street", SqlDbType.NVarChar).Value = info.street
                .Parameters.Add("@state", SqlDbType.NVarChar).Value = info.state
                .Parameters.Add("@city", SqlDbType.NVarChar).Value = info.city
                .Parameters.Add("@zip", SqlDbType.NVarChar).Value = info.zip
                .Parameters.Add("@email", SqlDbType.NVarChar).Value = info.email
            End With
        Catch ex As Exception

        End Try
        retVal = AccessDB(sqlCommand, False)
        Return retVal
    End Function
End Class
